package com.caresms.service.persistence;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import com.caresms.model.Keyword_Master;
import com.caresms.model.Sms_Master;
import com.caresms.model.impl.Keyword_MasterImpl;
import com.caresms.model.impl.Keyword_Sms_RelationImpl;
import com.caresms.model.impl.Sms_MasterImpl;

import com.liferay.portal.SystemException;
import com.liferay.portal.kernel.dao.orm.QueryPos;
import com.liferay.portal.kernel.dao.orm.QueryUtil;
import com.liferay.portal.kernel.dao.orm.SQLQuery;
import com.liferay.util.dao.orm.CustomSQLUtil;

import com.liferay.portal.kernel.dao.orm.Session;

public class Keyword_MasterFinderImpl extends Keyword_MasterPersistenceImpl
		implements Keyword_MasterFinder {

	public static String GET_BY = "getByB";
	public static String GET_BY_K = "getByK";

		private Connection getConnection() throws SQLException {
		return getDataSource().getConnection();
	}
	
	private void closeConnection(Connection con) throws SQLException {
		try {
			if (!con.isClosed()) {
				con.close();
			}
		} catch (SQLException e) {
			throw new SQLException(e);
		}
	}

	@SuppressWarnings("unchecked")
	public List<Keyword_Master> findBycat(long cid) throws SystemException {
		Session session = null;
		try {
			session = openSession();
			String sql = CustomSQLUtil.get(GET_BY);
			sql = sql + "where Cat_Id=" + cid + " ORDER BY KeywordName ASC";
			System.out.println("---" + sql);
			SQLQuery q = session.createSQLQuery(sql);
			q.addEntity("Keyword_Master", Keyword_MasterImpl.class);
			QueryPos qPos = QueryPos.getInstance(q);
			return (List<Keyword_Master>) QueryUtil.list(q, getDialect(),
					QueryUtil.ALL_POS, QueryUtil.ALL_POS);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	public long getByKeyname(String keyname) throws SystemException {
		Connection con = null;
		Statement st = null;
		ResultSet resultSet = null;
		String sql = null;
		int c=0;
		try {
			con = getConnection();
			sql = CustomSQLUtil.get(GET_BY);
			String key1=keyname;
			String key = "'" + keyname + "%'";
			sql = sql + "where KeywordName like" + key + " limit 1";
			st = con.createStatement();
			resultSet = st.executeQuery(sql);
			boolean flag=resultSet.wasNull();
			System.out.println("flag"+flag);
			
				if (resultSet.next()) {
					long keyid = resultSet.getLong("KeyId");
					System.out.println("id is" + keyid);
					return keyid;
				}
			
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		
		}finally{
			try {
				closeConnection(con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return 0;
	}

	@SuppressWarnings("unchecked")
	public List<Sms_Master> findBykeyid(long kid) throws SystemException {
		Session session = null;
		try {
			session = openSession();
			String sql = CustomSQLUtil.get(GET_BY_K);
			sql=sql+"where SmsId in (select SmsId from sms_Keyword_Sms_Relation where KeyId="+kid+")";
			//System.out.println("---" + sql);
			SQLQuery q = session.createSQLQuery(sql);
			
			q.addEntity("Sms_Master", Sms_MasterImpl.class);
			QueryPos qPos = QueryPos.getInstance(q);
			return (List<Sms_Master>) QueryUtil.list(q, getDialect(),
					QueryUtil.ALL_POS, QueryUtil.ALL_POS);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<Sms_Master> findSmsBycatid(long cid) throws SystemException {
		Session session = null;
		try {
			session = openSession();
			String sql = CustomSQLUtil.get(GET_BY_K);
			sql=sql+"where SmsId in (select SmsId from sms_Keyword_Sms_Relation where Cat_Id="+cid+")";
			//System.out.println("---" + sql);
			SQLQuery q = session.createSQLQuery(sql);
			
			q.addEntity("Sms_Master", Sms_MasterImpl.class);
			QueryPos qPos = QueryPos.getInstance(q);
			return (List<Sms_Master>) QueryUtil.list(q, getDialect(),
					QueryUtil.ALL_POS, QueryUtil.ALL_POS);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}
	
	public int countwhitespace(String keyname){
		int count =0;
		System.out.println("in counter");
		for (int i = 0; i< keyname.length(); i++) 
			if (Character.isWhitespace(keyname.charAt(i))) 
			count++;
		return count;
		
	}
}
